const pool = require('../database/conn')

const loginUser = async (username, password) => {
    const sql = 'SELECT id, name, username, avatar, create_time, privacy FROM users where username = ? and password = ?'
    const [rows] = await pool.query(sql, [username, password]);
    return rows
}
const getUserByUsername = async (username) => {
    const sql = 'SELECT count(*) count FROM `users` where username = ?'
    const [rows] = await pool.query(sql, [username])
    return rows
}

const createUser = async (fields) => {
    const sql = 'CALL sp_create_user(?,?,?,?)'
    const [ result ] = await pool.query(sql, fields)
    console.log(result)
    return result   
}

const getGroupsByUserId = async (uid) => {
    const sql = 'SELECT ' +
        '`group_members`.group_id, ' +
        '`groups`.group_pic, ' +
        '`groups`.group_name ' +
        'FROM `group_members` ' +
        'JOIN `groups` ' +
        'ON `groups`.id = `group_members`.group_id ' +
        'WHERE user_id = ?'
    const [rows] = await pool.query(sql, [uid])
    return rows
}

const getInboxByUserId = async (uid) => {
    const sql = 'SELECT id, head, content, create_time, type, isRead FROM inbox where uid = ? ORDER BY create_time desc'
    const [rows] = await pool.query(sql, [uid])
    return rows
}

const getUserProfileByUid = async (uid) => {
    const sql = 'SELECT name, username, avatar, create_time, privacy FROM `users` where id = ?'
    const [rows] = await pool.query(sql, [uid])
    return rows
}

const updateNicknameByUid = async (fields) => {
    const sql = 'UPDATE `users` SET name = ? where id = ?'
    const [result] = await pool.query(sql, fields)
    return result
}

const updateAvatarByUid = async (fields) => {
    const sql = 'UPDATE `users` SET avatar = ? WHERE id = ?'
    const result = await pool.query(sql, fields)
    return result
}
const getUserSettingByUid = async (uid) => {
    const sql = 'SELECT * FROM `user_settings` where uid = ?'
    const [rows] = await pool.query(sql, [uid])
    return rows
}

const changeUserThemeByUid = async (uid) => {
    const sql = 'UPDATE `user_settings` SET theme = IF(theme = 1, 0, 1) WHERE uid = ?'

    const [result] = await pool.query(sql, [uid])
    return result
}

const changePrivacyByUid = async (uid) => {
    const sql = 'UPDATE users SET privacy = IF(privacy = 1, 0, 1) WHERE id = ?'
    const result = await pool.query(sql, [uid])
    return result
}

const getConversationsByUid = async (uid) => {
    const sql = 'SELECT `dm_members`.conversation_id, `users`.id, `users`.name, `users`.avatar FROM `dm_members` JOIN users on `dm_members`.user_id = `users`.id WHERE conversation_id in (SELECT conversation_id FROM `dm_members` where user_id = ?) and user_id != ?'
    const [row] = await pool.query(sql, [uid, uid])
    return row
}

const getFullProfile = async (uid) => {
    const sql = 'SELECT users.id, users.name, users.avatar, users.create_time, user_profile.*  FROM users join user_profile on users.id = user_profile.uid where users.id = ?'
    const [row] = await pool.query(sql, [uid])
    return row
}

module.exports = {
    loginUser,
    createUser,
    getFullProfile,
    getUserByUsername,
    getGroupsByUserId,
    getInboxByUserId,
    getUserProfileByUid,
    updateNicknameByUid,
    updateAvatarByUid,
    getUserSettingByUid,
    changeUserThemeByUid,
    changePrivacyByUid,
    getConversationsByUid
}